SELECT
H.ihcode AS CollectionCode, 
S.accession AS CatalogNumber, 
ALLTRIM(IIF(ALLTRIM("" + GE.gename) <> "", GE.gename + " " + IIF(ALLTRIM("" + SP.sp1) <> "", SP.sp1 + " " + IIF(ALLTRIM("" + SP.sp2) <> "", IIF(ALLTRIM("" + SP.rank1) <> "", SP.rank1 + " ", "") + SP.sp2 + " ", "") + IIF(ALLTRIM("" + SP.sp3) <> "", IIF(ALLTRIM("" + SP.rank2) <> "", SP.rank2 + " ", "") + SP.sp3, ""), ""), F.faname)) AS ScientificName,
IIF(C.category = "V", "S", IIF(C.category <> "O", "", C.category) )AS BasisOfRecord,
F.faname AS Family,
GE.gename AS Genus,
SP.sp1 AS Species,
ALLTRIM(IIF(ALLTRIM("" + SP.sp2) <> "", IIF(ALLTRIM("" + SP.rank1) <> "", SP.rank1 + " ", "") + SP.sp2, "") + " " + IIF(ALLTRIM("" + SP.sp3) <> "", IIF(ALLTRIM("" + SP.rank2) <> "", SP.rank2 + " ", "") +  SP.sp3, "")) AS SubSpecies,
pv.namestring AS IdentifiedBy,
C.prefix + C.number + C.suffix AS ColectorNumber,
P.abbreviate AS Collector,
LEFT(IIF(C.year > 0, IIF(C.month > 0, IIF(C.day > 0, RIGHT("0" + ALLTRIM(STR(C.day)), 2) + "/", "") + RIGHT("0" + ALLTRIM(STR(C.month)), 2) + "/", "") + STR(C.year, 4), ""), 25) AS CollectionDate, 
CO.continent AS ContinentOcean,
ALLTRIM(IIF(ALLTRIM("" + CO.portuguese) <> "", CO.portuguese, CO.coname)) AS Country,
G.major AS State,
G.minor AS County,
ALLTRIM(G.locality + " " + left(C.locnotes,200)) AS Locality,
IIF(c.lat > 0, STR(FLOOR(ABS(c.lat )), 2, 0) + c.ns + SUBSTR(STR(ABS(c.lat) - FLOOR(ABS(c.lat)), 6, 4), 3, 2) + "'" + SUBSTR(STR(ABS(c.lat) - FLOOR(ABS(c.lat)), 6, 4), 5, 2) + '"', SPACE(9)) as latitude,
IIF(c.long > 0, STR(FLOOR(ABS(c.long)), 3, 0) + c.ew + SUBSTR(STR(ABS(c.long) - FLOOR(ABS(c.long)), 6, 4), 3, 2) + "'"  + SUBSTR(STR(ABS(c.long) - FLOOR(ABS(c.long)), 6, 4), 5, 2) + '"', SPACE(10)) as longitide,
alltrim(IIF(c.alt1 > 0, STR(c.alt1, 5) + IIF(c.alt2 > 0, "-" + STR(c.alt2, 5), SPACE(6)), SPACE(11))) as elevation,
alltrim(IIF(c.alt1 < 0 AND c.alt1 <> -9999, STR(c.alt1 * -1, 4) + IIF(c.alt2 < 0 AND c.alt2 <> -9999, "-" + STR(c.alt2 * -1, 4), SPACE(5)), SPACE(9))) as depth,
LEFT(ALLTRIM(c.notes), 254) as notes
FROM 
(((((((((
collections C LEFT JOIN specimens S ON C.brahms = S.id)
LEFT JOIN ih H ON S.hbcode = H.id)
LEFT JOIN gaz G ON C.gazcode = G.gazcode)
LEFT JOIN country CO ON G.conumber = CO.conumber)
LEFT JOIN species SP ON C.spnumber = SP.spnumber)
LEFT JOIN genus GE ON SP.gecode = GE.gecode)
LEFT JOIN family F ON GE.facode = F.facode)
LEFT JOIN peopleview PV ON C.pview = PV.id)
LEFT JOIN people P on PV.id = P.id)